package com.ctshk.rpc.hotel.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ctshk.rpc.hotel.dto.*;
import com.ctshk.rpc.hotel.dto.booking.HotelBookingProductDTO;
import com.ctshk.rpc.hotel.dto.booking.HotelProductUnitPriceDTO;
import com.ctshk.rpc.hotel.dto.h5.HotelProductDTO;
import com.ctshk.rpc.hotel.entity.HotelProduct;
import com.ctshk.rpc.hotel.req.HotelListProductReq;
import com.ctshk.rpc.hotel.req.HotelOrderQueryReq;
import com.ctshk.rpc.hotel.req.HotelReservertionReq;
import com.ctshk.rpc.hotel.req.booking.HotelDetailsQueryReq;
import com.ctshk.rpc.hotel.req.booking.HotelProductUnitPriceReq;
import com.ctshk.rpc.hotel.req.h5.HotelDetailsQueryH5Req;
import com.ctshk.rpc.hotel.req.h5.RoomDetailsQueryH5Req;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * 酒店产品 Mapper 接口
 * </p>
 *
 * @author yinhuan
 * @since 2021-03-03
 */
public interface HotelProductMapper extends BaseMapper<HotelProduct> {

    @Select("<script>"+"SELECT\n" +
            "\tt_hotel.id,\n" +
            "\tt_hotel_product.id as productId,\n" +
            "\tt_hotel_room_type.id as roomId,\n" +
            "\tt_hotel.city_name,\n" +
            "\tt_hotel.brand_name,\n" +
            "\tt_hotel.name as hotelName,\n" +
            "\tt_hotel_room_type.NAME as roomName,\n" +
            "\tt_hotel_product.NAME as productName,\n" +
            "\tt_hotel_room_type.bed_type,\n" +
            "\tt_hotel_product.has_breakfast as breakfast,\n" +
            "\tt_hotel_product.source_type as sourceType,\n" +
            "\tt_hotel_product.supplier_name,\n" +
            "\tt_hotel_product.product_number productCode,\n" +
            "\tt_hotel_product.target_population,\n" +
            "\tt_hotel_product.charge_user as chargerUser,\n" +
            "\tt_hotel_product.sale_status as saleStatus,\n" +
            "\tt_hotel_product.allow_package as allowPackage, \n" +
            "\tt_hotel_product.approval_status as approvalStatus, \n" +
            "\tt_hotel_product.is_recommend as recommendationStatus, \n" +
            "\tt_hotel_product.on_shelf_status as onShelfStatus \n" +
            "FROM\n" +
            "\tt_hotel,\n" +
            "\tt_hotel_room_type,\n" +
            "\tt_hotel_product\n" +
            "where\n" +
            "\tt_hotel.id = t_hotel_room_type.hotel_id \n" +
            "and \n" +
            "  t_hotel.id = t_hotel_product.hotel_id\n" +
            "and\n" +
            "\tt_hotel_room_type.id = t_hotel_product.room_id\n" +
            "and 1 = 1"+
            "<if test=\"req.cityName != null and req.cityName != ''\"> and (t_hotel.city_name LIKE CONCAT('%',#{req.cityName},'%'))</if> \n"+
            "<if test=\"req.brandName != null and req.brandName != ''\"> and (t_hotel.brand_name LIKE CONCAT('%',#{req.brandName},'%'))</if> \n"+
            "<if test=\"req.hotelName != null and req.hotelName != ''\"> and (t_hotel.name LIKE CONCAT('%',#{req.hotelName},'%'))</if> \n"+
            "<if test=\"req.roomName != null and req.roomName != ''\"> and (t_hotel_room_type.name LIKE CONCAT('%',#{req.roomName},'%'))</if> \n"+
            "<if test=\"req.supplierName != null and req.supplierName != ''\"> and (t_hotel_product.supplier_name LIKE CONCAT('%',#{req.supplierName},'%'))</if> \n"+
            "<if test=\"req.sourceType != null and req.sourceType != 0\"> and t_hotel_product.source_type = #{req.sourceType}</if> \n"+
            "<if test=\"req.saleStatus != null and req.saleStatus != ''\"> and t_hotel_product.status = #{req.saleStatus}</if> \n"+
            "<if test=\"req.name != null and req.name != ''\"> and t_hotel_product.name = #{req.name}</if> \n"+
            "<if test=\"req.recommendationStatus != null and req.recommendationStatus != ''\"> and t_hotel_product.is_recommend = #{req.recommendationStatus}</if> \n"+
            "<if test=\"req.approvalStatus != null and req.approvalStatus != ''\"> and t_hotel_product.approval_status = #{req.approvalStatus}</if> \n"+
            "<if test=\"req.allowPackage != null and req.allowPackage != ''\"> and t_hotel_product.allow_package = #{req.allowPackage}</if> \n" +
            "ORDER BY t_hotel_product.gmt_create DESC"
            +"</script>")
    Page<HotelProductListDTO> queryHotelProductList(HotelListProductReq req, Page<HotelProductListDTO> page);

    @Select("<script>"+"SELECT\n" +
            "\tt_hotel.id,\n" +
            "\tt_hotel_product.id as productId,\n" +
            "\tt_hotel_room_type.id as roomId,\n" +
            "\tt_hotel.city_name,\n" +
            "\tt_hotel.brand_name,\n" +
            "\tt_hotel.name as hotelName,\n" +
            "\tt_hotel_room_type.NAME as roomName,\n" +
            "\tt_hotel_product.NAME as productName,\n" +
            "\tt_hotel_room_type.bed_type,\n" +
            "\tt_hotel_product.has_breakfast as breakfast,\n" +
            "\tt_hotel.source_type as sourceType,\n" +
            "\tt_hotel_product.supplier_name,\n" +
            "\tt_hotel_product.product_number productCode,\n" +
            "\tt_hotel_product.target_population,\n" +
            "\tt_hotel_product.charge_user as chargerUser,\n" +
            "\tt_hotel_product.sale_status as saleStatus,\n" +
            "\tt_hotel_product.allow_package as allowPackage, \n" +
            "\tt_hotel_product.approval_status as approvalStatus, \n" +
            "\tt_hotel_product.is_recommend as recommendationStatus, \n" +
            "\tt_hotel_product.on_shelf_status as onShelfStatus \n" +
            "FROM\n" +
            "\tt_hotel,\n" +
            "\tt_hotel_room_type,\n" +
            "\tt_hotel_product\n" +
            "where\n" +
            "\tt_hotel.id = t_hotel_room_type.hotel_id \n" +
            "and \n" +
            "  t_hotel.id = t_hotel_product.hotel_id\n" +
            "and\n" +
            "\tt_hotel_room_type.id = t_hotel_product.room_id\n" +
            "and t_hotel_product.status = 1 "+
            "and t_hotel_product.approval_status = 3 "+
            "<if test=\"req.cityName != null and req.cityName != ''\"> and (t_hotel.city_name LIKE CONCAT('%',#{req.cityName},'%'))</if> \n"+
            "<if test=\"req.brandName != null and req.brandName != ''\"> and (t_hotel.brand_name LIKE CONCAT('%',#{req.brandName},'%'))</if> \n"+
            "<if test=\"req.hotelName != null and req.hotelName != ''\"> and (t_hotel.name LIKE CONCAT('%',#{req.hotelName},'%'))</if> \n"+
            "<if test=\"req.roomName != null and req.roomName != ''\"> and (t_hotel_room_type.name LIKE CONCAT('%',#{req.roomName},'%'))</if> \n"+
            "<if test=\"req.supplierName != null and req.supplierName != ''\"> and (t_hotel_product.supplier_name LIKE CONCAT('%',#{req.supplierName},'%'))</if> \n"+
            "<if test=\"req.sourceType != null and req.sourceType != ''\"> and t_hotel_product.source_type = #{req.sourceType}</if> \n"+
            "<if test=\"req.saleStatus != null and req.saleStatus != ''\"> and t_hotel_product.status = #{req.saleStatus}</if> \n"+
            "<if test=\"req.name != null and req.name != ''\"> and t_hotel_product.name = #{req.name}</if> \n"+
            "<if test=\"req.recommendationStatus != null and req.recommendationStatus != ''\"> and t_hotel_product.is_recommend = #{req.recommendationStatus}</if> \n"+
            "<if test=\"req.approvalStatus != null and req.approvalStatus != ''\"> and t_hotel_product.approval_status = #{req.approvalStatus}</if> \n"+
            "<if test=\"req.allowPackage != null and req.allowPackage != ''\"> and t_hotel_product.allow_package = #{req.allowPackage}</if> \n"+
            "<if test=\"req.productName != null and req.productName != ''\"> and t_hotel_product.name LIKE CONCAT('%',#{req.productName},'%')</if> \n"+
            "ORDER BY t_hotel_product.gmt_create DESC"
            +"</script>")
    Page<HotelProductListDTO> queryHotelProductListByStatus(HotelListProductReq req, Page<HotelProductListDTO> page);

//    @Select("<script>"+"SELECT\n" +
//            "\tt_hotel_product.id as productId,\n" +
//            "\tt_hotel_product.NAME as productName,\n" +
//            "\tt_hotel_product.has_breakfast as breakfast,\n" +
//            "\tt_hotel_product.supplier_name,\n" +
//            "\tt_hotel_product.target_population,\n" +
//            "\tt_hotel_product.charge_user as chargerUser,\n" +
//            "\tt_hotel_product.STATUS as saleStatus,\n" +
//            "\tt_hotel_product.allow_package as allowPackage, \n" +
//            "\tt_hotel_product.approval_status as approvalStatus \n" +
//            "FROM\n" +
//            "\tt_hotel_product\n" +
//            "where\n" +
//            "1=1"+
//            "<if test=\"req.cityName != null and req.cityName != ''\"> and (t_hotel.city_name LIKE CONCAT('%',#{req.cityName},'%'))</if> \n"+
//            "<if test=\"req.brandName != null and req.brandName != ''\"> and (t_hotel.brand_name LIKE CONCAT('%',#{req.brandName},'%'))</if> \n"+
//            "<if test=\"req.hotelName != null and req.hotelName != ''\"> and (t_hotel.name LIKE CONCAT('%',#{req.hotelName},'%'))</if> \n"+
//            "<if test=\"req.roomName != null and req.roomName != ''\"> and (t_hotel_room_type.name LIKE CONCAT('%',#{req.roomName},'%'))</if> \n"+
//            "<if test=\"req.supplierName != null and req.supplierName != ''\"> and (t_hotel_product.supplier_name LIKE CONCAT('%',#{req.supplierName},'%'))</if> \n"+
//            "<if test=\"req.sourceType != null and req.sourceType != ''\"> and t_hotel_product.source_type = #{req.sourceType}</if> \n"+
//            "<if test=\"req.saleStatus != null and req.saleStatus != ''\"> and t_hotel_product.status = #{req.saleStatus}</if> \n"+
//            "<if test=\"req.name != null and req.name != ''\"> and t_hotel_product.name = #{req.name}</if> \n"+
//            "<if test=\"req.approvalStatus != null and req.approvalStatus != ''\"> and t_hotel_product.approval_status = #{req.approvalStatus}</if> \n"+
//            "<if test=\"req.allowPackage != null and req.allowPackage != ''\"> and t_hotel_product.allow_package = #{req.allowPackage}</if> \n"
//            +"</script>")
//    Page<HotelProductListDTO> queryHotelProductList(HotelListProductReq req, Page<HotelProductListDTO> page);

    /**
     * 查询酒店推荐列表(至多15个)-废弃不用
     * @return
     */
    @Select("<script>"+
            "select h.id as hotelId, h.name as hotelName,h.en_name as hotelNameEn,h.location, h.star_level,h.star_level_id, b.id productId,b.supplier_id,b.source_type,b.min_price productPrice\n" +
            "from t_hotel_product_cost a\n" +
            "left join t_hotel_product b on a.hotel_product_id = b.id\n" +
            "left join t_hotel_product_cost_date c on a.id  = c.cost_id\n" +
            "left join t_hotel h on b.hotel_id = h.id\n" +
            "WHERE b.is_deleted = 0\n" +
            "AND b.is_recommend = 1\n" +
            "AND c.date_begin =CURDATE() and c.date_end =CURDATE()\n" +
            "group by b.id order by b.gmt_create desc limit 15"+
            "</script>")
    List<RecommendedHotelDTO> queryRecommendedHotel();

    /**
     * 预订中心-根据产品表查询推荐酒店ids(至多15个)
     * @return
     */
    @Select("<script>"+
            "SELECT" +
            "   DISTINCT thp.hotel_id " +
            "FROM" +
            "   t_hotel_product thp" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_channel_offline_self_custom thpcos ON thpcos.increase_strategy_id = thps.id " +
            "WHERE" +
                // 下方条件是获取最新推荐的酒店产品时间
            "   thp.recommend_time in (select MAX(recommend_time) from t_hotel_product where is_recommend = 1 GROUP BY hotel_id)" +
                // 下方条件是校验当前时间存在酒店成本有效期内
            "   AND thpcd.date_begin &lt;= #{nowTime} AND thpcd.date_end &gt;= #{nowTime}" +
                // 下方条件是校验当前时间存在酒店加幅策略有效期内
            "   AND thpsd.date_begin &lt;= #{nowTime} AND thpsd.date_end &gt;= #{nowTime}" +
                // 不查询禁售产品
            "   AND thps.self_sale_permission_type &lt;&gt; 1" +
                // 查询产品指定可销售的人员
            "   AND IF(self_sale_permission_type = 2,1=1,thpcos.relation_id IN " +
            "     <foreach collection='relationIds' item='id' open='(' separator=',' close=')'>" +
            "         #{id}" +
            "     </foreach>" + " )" +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "ORDER BY thp.recommend_time DESC" +
            "   LIMIT 15;" +
            "</script>")
    List<Long> queryRecommendedIds(@Param("relationIds") List<Long> relationIds, @Param("nowTime") String nowTime);

    /**
     * 获取推荐酒店ids
     * @return
     */
    @Select("<script>"+
            "SELECT" +
            "   DISTINCT thp.hotel_id " +
            "FROM" +
            "   t_hotel_product thp" +
            "   LEFT JOIN t_hotel th ON th.id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_channel_online_self_platform thpcosp ON thpcosp.increase_strategy_id = thps.id " +
            "WHERE" +
            // 下方条件是获取最新推荐的酒店产品时间
            "   thp.recommend_time in (select MAX(recommend_time) from t_hotel_product where is_recommend = 1 GROUP BY hotel_id)" +
            // 下方条件是校验当前时间存在酒店成本有效期内
            "   AND thpcd.date_begin &lt;= #{nowTime} AND thpcd.date_end &gt;= #{nowTime}" +
            // 下方条件是校验当前时间存在酒店加幅策略有效期内
            "   AND thpsd.date_begin &lt;= #{nowTime} AND thpsd.date_end &gt;= #{nowTime}" +
            // 查询C端渠道产品
            "   AND (thpcosp.channel_id = #{channelApp} OR thpcosp.channel_id = #{channelPc})" +
            // 搜索条件-城市
            "   <if test=\"cityName != null and cityName != ''\">" +
            "     AND th.city_name LIKE CONCAT('%',#{cityName},'%') " +
            "   </if>" +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "ORDER BY thp.recommend_time DESC" +
            "</script>")
    List<Long> queryRecommendedIdsH5(@Param("nowTime") String nowTime, @Param("cityName") String cityName, @Param("channelApp") Long channelApp, @Param("channelPc") Long channelPc);

    /**
     * 预订中心-根据酒店查询产品和最低的价格
     * @param relationIds
     * @param nowTime
     * @param hotelId
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   th.id," +
            "   thp.id productId," +
            "   th.brand_id," +
            "   th.brand_name," +
            "   th.`name`," +
            "   th.en_name," +
            "   th.city_id," +
            "   th.city_name," +
            "   th.area_id," +
            "   th.area_name," +
            "   th.location," +
            "   th.business_district_id," +
            "   th.business_district_name," +
            "   th.loc_to_district_distance," +
            "   th.star_level," +
            "   th.cover_map," +
            "   IF( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "   thp.reserve_charge_type," +
            "   thp.reserve_charge," +
            "   thpisr.price_type," +
            "   thpisr.price increasePrice," +
            // 获取酒店房间产品价格,实际售卖价格=成本价(需转换成HKD)+服务费+加幅
            "   IFNULL((" +
                // 计算成本价(成本价*汇率)
            "   IF( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
                // 服务费计算
            "   IF( thp.reserve_charge_type != 2, IF( thp.reserve_charge != 0, thp.reserve_charge, 0 ), thpc.price*(IF( thp.reserve_charge != 0, thp.reserve_charge, 0 )/100) ) +" +
                // 加幅价计算
            "   IF( thpisr.price_type != 2, IF( thpisr.price != 0, thpisr.price, 0 ), thpc.price*(IF( thpisr.price != 0, thpisr.price, 0 )/100) )" +
            "   ), 0) price " +
            "FROM" +
            "   t_hotel th" +
            "   LEFT JOIN t_hotel_product thp ON thp.hotel_id = th.id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_channel_offline_self_custom thpcos ON thpcos.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcos.id " +
            "WHERE" +
            "   th.id = #{hotelId} " +
            // 下方条件是校验当前时间存在酒店成本有效期内
            "   AND thpcd.date_begin &lt;= #{nowTime} AND thpcd.date_end &gt;= #{nowTime}" +
            // 下方条件是校验当前时间存在酒店加幅策略有效期内
            "   AND thpsd.date_begin &lt;= #{nowTime} AND thpsd.date_end &gt;= #{nowTime}" +
            // 不查询禁售产品
            "   AND thps.self_sale_permission_type &lt;&gt; 1" +
            // 查询产品指定可销售的人员
            "   AND IF(self_sale_permission_type = 2,1=1,thpcos.relation_id IN " +
            "     <foreach collection='relationIds' item='id' open='(' separator=',' close=')'>" +
            "         #{id}" +
            "     </foreach>" + " )" +
            "   AND th.is_deleted = 0 " +
            "   AND thp.is_deleted = 0 " +
            "GROUP BY" +
            "   thp.id" +
            "   ORDER BY price " +
            "   LIMIT 1" +
            "</script>")
    HotelQueryListDTO queryProductsMin(@Param("relationIds") List<Long> relationIds, @Param("nowTime") String nowTime, @Param("hotelId") Long hotelId);

    /**
     * 预订中心-根据酒店查询产品和最低的价格
     * @param nowTime
     * @param hotelId
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   th.id," +
            "   thp.id productId," +
            "   th.brand_id," +
            "   th.brand_name," +
            "   th.`name`," +
            "   th.en_name," +
            "   th.city_id," +
            "   th.city_name," +
            "   th.area_id," +
            "   th.area_name," +
            "   th.location," +
            "   th.business_district_id," +
            "   th.business_district_name," +
            "   th.loc_to_district_distance," +
            "   th.star_level," +
            "   th.cover_map," +
            "   IF( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "   thp.reserve_charge_type," +
            "   thp.reserve_charge," +
            "   thpisr.price_type," +
            "   thpisr.price increasePrice," +
            // 获取酒店房间产品价格,实际售卖价格=成本价(需转换成HKD)+服务费+加幅
            "   (" +
            // 计算成本价(成本价*汇率)
            "   IF( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
            // 服务费计算
            "   IF( thp.reserve_charge_type != 2, IF( thp.reserve_charge != 0, thp.reserve_charge, 0 ), thpc.price*(IF( thp.reserve_charge != 0, thp.reserve_charge, 0 )/100) ) +" +
            // 加幅价计算
            "   IF( thpisr.price_type != 2, IF( thpisr.price != 0, thpisr.price, 0 ), thpc.price*(IF( thpisr.price != 0, thpisr.price, 0 )/100) )" +
            "   ) price " +
            "FROM" +
            "   t_hotel th" +
            "   LEFT JOIN t_hotel_product thp ON thp.hotel_id = th.id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_channel_offline_self_custom thpcos ON thpcos.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcos.id " +
            "WHERE" +
            "   th.id = #{hotelId} " +
            // 下方条件是校验当前时间存在酒店成本有效期内
            "   AND thpcd.date_begin &lt;= #{nowTime} AND thpcd.date_end &gt;= #{nowTime}" +
            // 下方条件是校验当前时间存在酒店加幅策略有效期内
            "   AND thpsd.date_begin &lt;= #{nowTime} AND thpsd.date_end &gt;= #{nowTime}" +
            "   AND th.is_deleted = 0 " +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "GROUP BY" +
            "   thp.id" +
            "   ORDER BY price " +
            "   LIMIT 1" +
            "</script>")
    HotelQueryListDTO queryProductsMinH5(@Param("nowTime") String nowTime, @Param("hotelId") Long hotelId);

    /**
     * 预订中心-搜索酒店列表
     * @param page
     * @param relationIds
     * @param req
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   * " +
            "FROM" +
            "   (" +
            "   SELECT" +
            "      th.id," +
            "      thp.id productId," +
            "      th.brand_id," +
            "      th.brand_name," +
            "      th.`name`," +
            "      th.en_name," +
            "      th.city_id," +
            "      th.city_name," +
            "      th.area_id," +
            "      th.area_name," +
            "      th.location," +
            "      th.business_district_id," +
            "      th.business_district_name," +
            "      th.loc_to_district_distance," +
            "      th.star_level," +
            "      th.cover_map," +
            "   IF" +
            "      ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "      thp.reserve_charge_type," +
            "      thp.reserve_charge," +
            "      thpisr.price_type increaseType," +
            "      thpisr.price increasePrice," +
            // 获取酒店房间产品最低价格,实际售卖价格=成本价(需转换成HKD)+税费+服务费+加幅
            "      MIN(" +
            "         (" +
                        // 成本价
            "         IF" +
            "            ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
                        // 税费
            "         IF" +
            "            ( thp.taxation != 0, thp.taxation, 0 ) +" +
                        // 服务费
            "         IF" +
            "            (" +
            "               thp.reserve_charge_type != 2," +
            "            IF" +
            "               ( thp.reserve_charge != 0, thp.reserve_charge, 0 )," +
            "               thpc.price *(" +
            "               IF" +
            "                  ( thp.reserve_charge != 0, thp.reserve_charge, 0 )/ 100 " +
            "               )) +" +
                        // 加幅费
            "         IF" +
            "            (" +
            "               thpisr.price_type != 2," +
            "            IF" +
            "               ( thpisr.price != 0, thpisr.price, 0 )," +
            "               thpc.price *(" +
            "               IF" +
            "                  ( thpisr.price != 0, thpisr.price, 0 )/ 100 " +
            "               ))) " +
            "      ) price," +
            "      ( SELECT COUNT(*) FROM t_hotel_room_type WHERE hotel_id = th.id ) roomNum " +
            "   FROM" +
            "      t_hotel th" +
            "      LEFT JOIN t_hotel_product thp ON thp.hotel_id = th.id" +
            "      LEFT JOIN t_hotel_facilities thf ON thf.hotel_id = th.id" +
            "      LEFT JOIN t_hotel_room_type thrt ON thrt.hotel_id = th.id" +
            "      LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "      LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "      LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "      LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "      LEFT JOIN t_hotel_product_channel_offline_self_custom thpcos ON thpcos.increase_strategy_id = thps.id" +
            "      LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcos.id " +
            "   WHERE" +
            "   thp.approval_status = 3" +
            "   AND " +
            // 成本价需要在用户输入的时间段范围内(通过判断产品成本是否在用户选择的所有时间段内,搜索出来的成本数要等于用户选择天数)
            "   (SELECT count(thpcd2.id) FROM t_hotel_product_cost thpc2 LEFT JOIN t_hotel_product_cost_date thpcd2 ON thpcd2.cost_id = thpc2.id WHERE thpc2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpcd2.date_begin &lt;= #{date} AND thpcd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND " +
            // 加幅策略需要在用户输入的时间段范围内(通过判断产品加幅是否在用户选择的所有时间段内,搜索出来的加幅数要等于用户选择天数)
            "   (SELECT count(thpsd2.id) FROM t_hotel_product_strategy thps2 LEFT JOIN t_hotel_product_strategy_date thpsd2 ON thpsd2.increase_strategy_id = thps2.id WHERE thps2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpsd2.date_begin &lt;= #{date} AND thpsd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
                    // 不查询禁售产品
            "      AND thps.self_sale_permission_type &lt;&gt; 1" +
                    // 查询产品指定可销售的人员
            "      AND IF(self_sale_permission_type = 2,1=1,thpcos.relation_id IN " +
            "       <foreach collection='relationIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" + " )" +
                    // 预订查询页面-左边搜索条件-城市
            "      <if test=\"req.cityName != null and req.cityName != ''\">" +
            "       AND th.city_name LIKE CONCAT('%',#{req.cityName},'%') " +
            "      </if>" +
                    // 预订查询页面-左边搜索条件-酒店名/位置
            "      <if test=\"req.searchName != null and req.searchName != ''\">" +
            "       AND ( th.`name` LIKE CONCAT('%',#{req.searchName},'%') OR th.en_name LIKE CONCAT('%',#{req.searchName},'%') OR th.business_district_name LIKE CONCAT('%',#{req.searchName},'%') ) " +
            "      </if>" +
                    // 预订查询页面-搜索条件-酒店星级
            "       <if test=\"req.starLevels != null and !req.starLevels.isEmpty()\">" +
            "      AND th.star_level IN " +
            "       <foreach collection='req.starLevels' item='level' open='(' separator=',' close=')'>" +
            "           #{level}" +
            "       </foreach>" +
            "       </if>" +
                    // 预订查询页面-右边搜索条件-酒店设施
            "       <if test=\"req.facilitiesIds != null and !req.facilitiesIds.isEmpty()\">" +
            "      AND thf.facilities_id IN " +
            "       <foreach collection='req.facilitiesIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" +
            "       </if>" +
                    // 预订查询页面-右边搜索条件-酒店位置
            "       <if test=\"req.businessDistrictIds != null and !req.businessDistrictIds.isEmpty()\">" +
            "      AND th.business_district_id IN " +
            "       <foreach collection='req.businessDistrictIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" +
            "       </if>" +
                    // 预订查询页面-左边搜索条件-入住人数(总人数/房间数)
            "      AND thrt.max_adult &gt;= #{req.peopleNum}" +
                    // 预订查询页面-是否需要加床,入住人数有余数时使用
            "      <if test=\"req.isAddBed == true\">" +
            "       AND thrt.can_add_bed = 1" +
            "      </if>" +
            "   AND th.is_deleted = 0 " +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "   GROUP BY" +
            "      th.id " +
            "   ) hotel " +
            "WHERE" +
                // 预订查询页面-左边搜索条件-房间数
            "   hotel.roomNum >= #{req.roomNum} " +
                // 预订查询页面-右边搜索条件-最低价
            "   <if test=\"req.minPrice != null and req.minPrice != 0\">" +
            "       AND hotel.price &gt;= #{req.minPrice} " +
            "   </if>" +
                // 预订查询页面-右边搜索条件-最高价
            "   <if test=\"req.maxPrice != null and req.maxPrice != 0\">" +
            "       AND hotel.price &lt;= #{req.maxPrice} " +
            "   </if>" +
            "</script>")
    Page<HotelQueryListDTO> queryHotels(Page<HotelQueryListDTO> page, @Param("dates") List<LocalDate> dates, @Param("relationIds") List<Long> relationIds, @Param("req") HotelOrderQueryReq req);

    /**
     * 预订中心-搜索酒店列表
     * @param page
     * @param relationIds
     * @param req
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   * " +
            "FROM" +
            "   (" +
            "   SELECT" +
            "      th.id," +
            "      thp.id productId," +
            "      th.brand_id," +
            "      th.brand_name," +
            "      th.`name`," +
            "      th.en_name," +
            "      th.city_id," +
            "      th.city_name," +
            "      th.area_id," +
            "      th.area_name," +
            "      th.location," +
            "      th.business_district_id," +
            "      th.business_district_name," +
            "      th.loc_to_district_distance," +
            "      th.star_level," +
            "      th.cover_map," +
            "   IF" +
            "      ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "      thp.reserve_charge_type," +
            "      thp.reserve_charge," +
            "      thpisr.price_type increaseType," +
            "      thpisr.price increasePrice," +
            "      thp.is_sec_check isSecCheck," +
            "      thp.allow_refund allowRefund," +
            // 获取酒店房间产品最低价格,实际售卖价格=成本价(需转换成HKD)+税费+服务费+加幅
            "      MIN(" +
            "         (" +
            // 成本价
            "         IF" +
            "            ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
            // 税费
            "         IF" +
            "            ( thp.taxation != 0, thp.taxation, 0 ) +" +
            // 服务费
            "         IF" +
            "            (" +
            "               thp.reserve_charge_type != 2," +
            "            IF" +
            "               ( thp.reserve_charge != 0, thp.reserve_charge, 0 )," +
            "               thpc.price *(" +
            "               IF" +
            "                  ( thp.reserve_charge != 0, thp.reserve_charge, 0 )/ 100 " +
            "               )) +" +
            // 加幅费
            "         IF" +
            "            (" +
            "               thpisr.price_type != 2," +
            "            IF" +
            "               ( thpisr.price != 0, thpisr.price, 0 )," +
            "               thpc.price *(" +
            "               IF" +
            "                  ( thpisr.price != 0, thpisr.price, 0 )/ 100 " +
            "               ))) " +
            "      ) price," +
            "      ( SELECT COUNT(*) FROM t_hotel_room_type WHERE hotel_id = th.id ) roomNum " +
            "   FROM" +
            "      t_hotel th" +
            "      LEFT JOIN t_hotel_product thp ON thp.hotel_id = th.id" +
            "      LEFT JOIN t_hotel_facilities thf ON thf.hotel_id = th.id" +
            "      LEFT JOIN t_hotel_room_type thrt ON thrt.hotel_id = th.id" +
            "      LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "      LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "      LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "      LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "      LEFT JOIN t_hotel_product_channel_online_self_platform thpcosp ON thpcosp.increase_strategy_id = thps.id " +
            "      LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcosp.id " +
            "   WHERE" +
            "      thp.approval_status = 3 AND " +
            // 成本价需要在用户输入的时间段范围内(通过判断产品成本是否在用户选择的所有时间段内,搜索出来的成本数要等于用户选择天数)
            "   (SELECT count(thpcd2.id) FROM t_hotel_product_cost thpc2 LEFT JOIN t_hotel_product_cost_date thpcd2 ON thpcd2.cost_id = thpc2.id WHERE thpc2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpcd2.date_begin &lt;= #{date} AND thpcd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND " +
            // 加幅策略需要在用户输入的时间段范围内(通过判断产品加幅是否在用户选择的所有时间段内,搜索出来的加幅数要等于用户选择天数)
            "   (SELECT count(thpsd2.id) FROM t_hotel_product_strategy thps2 LEFT JOIN t_hotel_product_strategy_date thpsd2 ON thpsd2.increase_strategy_id = thps2.id WHERE thps2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpsd2.date_begin &lt;= #{date} AND thpsd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            // 查询C端渠道产品
            "      AND (thpcosp.channel_id = #{channelApp} OR thpcosp.channel_id = #{channelPc})" +
            // 预订查询页面-左边搜索条件-城市
            "      <if test=\"req.cityName != null and req.cityName != ''\">" +
            "       AND th.city_name LIKE CONCAT('%',#{req.cityName},'%') " +
            "      </if>" +
            // 预订查询页面-左边搜索条件-酒店名/位置
            "      <if test=\"req.searchName != null and req.searchName != ''\">" +
            "       AND ( th.`name` LIKE CONCAT('%',#{req.searchName},'%') OR th.en_name LIKE CONCAT('%',#{req.searchName},'%') OR th.business_district_name LIKE CONCAT('%',#{req.searchName},'%') ) " +
            "      </if>" +
            // 预订查询页面-搜索条件-酒店星级
            "       <if test=\"req.starLevels != null and !req.starLevels.isEmpty()\">" +
            "      AND th.star_level IN " +
            "       <foreach collection='req.starLevels' item='level' open='(' separator=',' close=')'>" +
            "           #{level}" +
            "       </foreach>" +
            "       </if>" +
            // 预订查询页面-右边搜索条件-酒店设施
            "       <if test=\"req.facilitiesIds != null and !req.facilitiesIds.isEmpty()\">" +
            "      AND thf.facilities_id IN " +
            "       <foreach collection='req.facilitiesIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" +
            "       </if>" +
            // 预订查询页面-右边搜索条件-酒店位置
            "       <if test=\"req.businessDistrictIds != null and !req.businessDistrictIds.isEmpty()\">" +
            "      AND th.business_district_id IN " +
            "       <foreach collection='req.businessDistrictIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" +
            "       </if>" +
            // 预订查询页面-左边搜索条件-入住人数(总人数/房间数)
            "      AND thrt.max_adult &gt;= #{req.peopleNum}" +
            // 预订查询页面-是否需要加床,入住人数有余数时使用
            "      <if test=\"req.isAddBed == true\">" +
            "       AND thrt.can_add_bed = 1" +
            "      </if>" +
            "   AND th.is_deleted = 0 " +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "   GROUP BY" +
            "      th.id " +
            "   ) hotel " +
            "WHERE" +
            // 预订查询页面-左边搜索条件-房间数
            "   hotel.roomNum >= #{req.roomNum} " +
            // 预订查询页面-右边搜索条件-最低价
            "   <if test=\"req.minPrice != null and req.minPrice != 0\">" +
            "       AND hotel.price &gt;= #{req.minPrice} " +
            "   </if>" +
            // 预订查询页面-右边搜索条件-最高价
            "   <if test=\"req.maxPrice != null and req.maxPrice != 0\">" +
            "       AND hotel.price &lt;= #{req.maxPrice} " +
            "   </if>" +
            "</script>")
    Page<HotelQueryListDTO> queryHotelsH5(Page<HotelQueryListDTO> page, @Param("req") com.ctshk.rpc.hotel.req.h5.HotelQueryReq req, @Param("dates") List<LocalDate> dates, @Param("channelApp") Long channelApp, @Param("channelPc") Long channelPc);

    /**
     * 预订中心-获取酒店产品详情
     * @param relationIds
     * @param req
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   thp.id," +
            "   thp.`name`," +
            "   thp.has_breakfast," +
            "   thp.hotel_id," +
            "   thp.supplier_name," +
            "   thrt.bed_type," +
            "   thrt.max_adult," +
            "   thp.target_population," +
            "   thpisr.saleable_number," +
            "   thpc.stock costStock," +
            "   thp.is_sec_check," +
            "   thp.customer_notice," +
            "   thpc.currency_id costCurrencyId," +
            "   thpc.currency_name costCurrencyName," +
            "   thpc.exchange_rate costExchangeRate," +
            // 获取酒店房间产品价格,实际售卖价格=成本价(需转换成HKD)+服务费+加幅
            "IF" +
            "   ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "   thp.reserve_charge_type," +
            "   thp.reserve_charge," +
            "   thpisr.price_type increaseType," +
            "   thpisr.price increasePrice," +
            "      (" +
                    // 成本价
            "      IF" +
            "         ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
                    // 税费
            "      IF" +
            "         ( thp.taxation != 0, thp.taxation, 0 ) +" +
                    // 服务费
            "      IF" +
            "         (" +
            "            thp.reserve_charge_type != 2," +
            "         IF" +
            "            ( thp.reserve_charge != 0, thp.reserve_charge, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thp.reserve_charge != 0, thp.reserve_charge, 0 )/ 100 " +
            "            )) +" +
                    // 加幅
            "      IF" +
            "         (" +
            "            thpisr.price_type != 2," +
            "         IF" +
            "            ( thpisr.price != 0, thpisr.price, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thpisr.price != 0, thpisr.price, 0 )/ 100 " +
            "            ))" +
            "   ) price," +
                // 获取酒店所有房间数
            "   ( SELECT COUNT(*) FROM t_hotel_room_type WHERE hotel_id = thp.hotel_id ) roomNum," +
            // dateType==1跨多天查询
            "   <if test=\"dateType == 1\">" +
                    // 获取酒店产品剩余库存(用+号是因为获取所用的库存数都是以-号开头,如果还用-号计算则会变成相加库存)
            "       IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate}) = 0,thpc.stock,thpc.stock + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate})) remainStock, " +
                    // 获取用户剩余可售数(用+号是因为获取所用的可售数都是以-号开头,如果还用-号计算则会变成相加可售数)
            "       IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate} AND operator_id = #{userId}) = 0,thpisr.saleable_number,thpisr.saleable_number + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate})) remainSaleableNumber " +
            "   </if>" +
            // dateType==2按每天天查询
            "   <if test=\"dateType == 2\">" +
                    // 获取酒店产品剩余库存(用+号是因为获取所用的库存数都是以-号开头,如果还用-号计算则会变成相加库存) todo 这个通过循环去查询库存会存在一定问题,后续需要优化处理
            "       <foreach collection='dates' item='date' open='(' separator=',' close=')'>" +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date}) = 0,thpc.stock,thpc.stock + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date})) " +
            "       </foreach>" +
            "   remainStock, " +
                    // 获取用户剩余可售数(用+号是因为获取所用的可售数都是以-号开头,如果还用-号计算则会变成相加可售数) todo 这个通过循环去查询库存会存在一定问题,后续需要优化处理
            "       <foreach collection='dates' item='date' open='(' separator=',' close=')'>" +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date} AND operator_id = #{userId}) = 0,thpisr.saleable_number,thpisr.saleable_number + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date})) " +
            "       </foreach>" +
            "   remainSaleableNumber " +
            "   </if>" +
            "FROM" +
            "   t_hotel_product thp" +
            "   LEFT JOIN t_hotel_facilities thf ON thf.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_room_type thrt ON thrt.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_channel_offline_self_custom thpcos ON thpcos.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcos.id " +
            "WHERE" +
            "   thp.room_id = #{req.roomId}" +
            "   AND " +
            // 成本价需要在用户输入的时间段范围内(通过判断产品成本是否在用户选择的所有时间段内,搜索出来的成本数要等于用户选择天数)
            "   (SELECT count(thpcd2.id) FROM t_hotel_product_cost thpc2 LEFT JOIN t_hotel_product_cost_date thpcd2 ON thpcd2.cost_id = thpc2.id WHERE thpc2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpcd2.date_begin &lt;= #{date} AND thpcd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND " +
            // 加幅策略需要在用户输入的时间段范围内(通过判断产品加幅是否在用户选择的所有时间段内,搜索出来的加幅数要等于用户选择天数)
            "   (SELECT count(thpsd2.id) FROM t_hotel_product_strategy thps2 LEFT JOIN t_hotel_product_strategy_date thpsd2 ON thpsd2.increase_strategy_id = thps2.id WHERE thps2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpsd2.date_begin &lt;= #{date} AND thpsd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND thrt.max_adult &gt;= #{req.peopleNum}" +
                // 不查询禁售产品
            "   AND thps.self_sale_permission_type &lt;&gt; 1" +
                // 查询产品指定可销售的人员
            "   AND IF(self_sale_permission_type = 2,1=1,thpcos.relation_id IN " +
            "       <foreach collection='relationIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" + " )" +
                // 查询审核通过的酒店产品
            "   AND thp.approval_status = 3" +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "GROUP BY" +
            "   id" +
            "</script>")
    List<HotelBookingProductDTO> queryProducts(@Param("relationIds") List<Long> relationIds, @Param("req") HotelDetailsQueryReq req, @Param("userId") Long userId, @Param("dates") List<LocalDate> dates, @Param("dateType") int dateType);

    /**
     * 预订中心-获取酒店产品详情-C端H5
     * @param relationIds
     * @param req
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   thp.id," +
            "   thp.`name`," +
            "   thp.has_breakfast," +
            "   thp.hotel_id," +
            "   thp.supplier_name," +
            "   thrt.bed_type," +
            "   thrt.max_adult," +
            "   thp.target_population," +
            "   thpisr.saleable_number," +
            "   thpc.stock costStock," +
            "   thp.allow_refund," +
            "   thp.is_sec_check," +
            "   thp.customer_notice," +
            "   thpc.currency_id costCurrencyId," +
            "   thpc.currency_name costCurrencyName," +
            "   thpc.exchange_rate costExchangeRate," +
            // 获取酒店房间产品价格,实际售卖价格=成本价(需转换成HKD)+服务费+加幅
            "IF" +
            "   ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "   thp.reserve_charge_type," +
            "   thp.reserve_charge," +
            "   thpisr.price_type increaseType," +
            "   thpisr.price increasePrice," +
            "      (" +
            // 成本价
            "      IF" +
            "         ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
            // 税费
            "      IF" +
            "         ( thp.taxation != 0, thp.taxation, 0 ) +" +
            // 服务费
            "      IF" +
            "         (" +
            "            thp.reserve_charge_type != 2," +
            "         IF" +
            "            ( thp.reserve_charge != 0, thp.reserve_charge, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thp.reserve_charge != 0, thp.reserve_charge, 0 )/ 100 " +
            "            )) +" +
            // 加幅
            "      IF" +
            "         (" +
            "            thpisr.price_type != 2," +
            "         IF" +
            "            ( thpisr.price != 0, thpisr.price, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thpisr.price != 0, thpisr.price, 0 )/ 100 " +
            "            ))" +
            "   ) price," +
            // 获取酒店所有房间数
            "   ( SELECT COUNT(*) FROM t_hotel_room_type WHERE hotel_id = thp.hotel_id ) roomNum," +
            // dateType==1跨多天查询
            "   <if test=\"dateType == 1\">" +
                // 获取酒店产品剩余库存(用+号是因为获取所用的库存数都是以-号开头,如果还用-号计算则会变成相加库存)
            "   IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate}) = 0,thpc.stock,thpc.stock + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate})) remainStock, " +
                // 获取用户剩余可售数(用+号是因为获取所用的可售数都是以-号开头,如果还用-号计算则会变成相加可售数)
            "   IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate} AND operator_id = #{channelApp}) = 0,thpisr.saleable_number,thpisr.saleable_number + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{req.checkInDate} AND use_time &lt;= #{req.checkOutDate})) remainSaleableNumber " +
            "   </if>" +
            // dateType==2按每天天查询
            "   <if test=\"dateType == 2\">" +
                    // 获取酒店产品剩余库存(用+号是因为获取所用的库存数都是以-号开头,如果还用-号计算则会变成相加库存) todo 这个通过循环去查询库存会存在一定问题,后续需要优化处理
            "       <foreach collection='dates' item='date' open='(' separator=',' close=')'>" +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date}) = 0,thpc.stock,thpc.stock + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date})) " +
            "       </foreach>" +
            "   remainStock, " +
                    // 获取用户剩余可售数(用+号是因为获取所用的可售数都是以-号开头,如果还用-号计算则会变成相加可售数) todo 这个通过循环去查询库存会存在一定问题,后续需要优化处理
            "       <foreach collection='dates' item='date' open='(' separator=',' close=')'>" +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date} AND operator_id = #{channelApp}) = 0,thpisr.saleable_number,thpisr.saleable_number + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date})) " +
            "       </foreach>" +
            "   remainSaleableNumber " +
            "   </if>" +
            "FROM" +
            "   t_hotel_product thp" +
            "   LEFT JOIN t_hotel_facilities thf ON thf.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_room_type thrt ON thrt.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_channel_online_self_platform thpcosp ON thpcosp.increase_strategy_id = thps.id " +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcosp.id " +
            "WHERE" +
            "   thp.room_id = #{req.roomId}" +
            "   AND " +
            // 成本价需要在用户输入的时间段范围内(通过判断产品成本是否在用户选择的所有时间段内,搜索出来的成本数要等于用户选择天数)
            "   (SELECT count(thpcd2.id) FROM t_hotel_product_cost thpc2 LEFT JOIN t_hotel_product_cost_date thpcd2 ON thpcd2.cost_id = thpc2.id WHERE thpc2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpcd2.date_begin &lt;= #{date} AND thpcd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND " +
            // 加幅策略需要在用户输入的时间段范围内(通过判断产品加幅是否在用户选择的所有时间段内,搜索出来的加幅数要等于用户选择天数)
            "   (SELECT count(thpsd2.id) FROM t_hotel_product_strategy thps2 LEFT JOIN t_hotel_product_strategy_date thpsd2 ON thpsd2.increase_strategy_id = thps2.id WHERE thps2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpsd2.date_begin &lt;= #{date} AND thpsd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND thrt.max_adult &gt;= #{req.peopleNum}" +
            // 查询C端渠道产品
            "   AND (thpcosp.channel_id = #{channelApp} OR thpcosp.channel_id = #{channelPc})" +
            // 查询审核通过的酒店产品
            "   AND thp.approval_status = 3" +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "GROUP BY" +
            "   id" +
            "</script>")
    List<HotelBookingProductDTO> queryProductsH5(@Param("req") HotelDetailsQueryH5Req req, @Param("dates") List<LocalDate> dates, @Param("dateType") int dateType, @Param("channelApp") Long channelApp, @Param("channelPc") Long channelPc);

    /**
     * 获取酒店产品详情-C端H5-填写订单页使用
     * @param req
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   thp.id," +
            "   thp.`name`," +
            "   thp.has_breakfast," +
            "   thp.hotel_id," +
            "   thp.room_id," +
            "   thp.supplier_name," +
            "   thrt.bed_type," +
            "   thrt.max_adult," +
            "   thp.target_population," +
            "   thpisr.saleable_number," +
            "   thpc.stock costStock," +
            "   thp.is_sec_check," +
            "   thp.allow_refund," +
            "   thp.customer_notice," +
            "   thpc.currency_id costCurrencyId," +
            "   thpc.currency_name costCurrencyName," +
            "   thpc.exchange_rate costExchangeRate," +
            // 获取酒店房间产品价格,实际售卖价格=成本价(需转换成HKD)+服务费+加幅
            "IF" +
            "   ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "   thpisr.price taxation," +
            "      (" +
            // 成本价
            "      IF" +
            "         ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
            // 税费-C端填写订单页的单价价格不加上税费,税费需单独显示
            // "      IF" +
            // "         ( thp.taxation != 0, thp.taxation, 0 ) +" +
            // 服务费
            "      IF" +
            "         (" +
            "            thp.reserve_charge_type != 2," +
            "         IF" +
            "            ( thp.reserve_charge != 0, thp.reserve_charge, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thp.reserve_charge != 0, thp.reserve_charge, 0 )/ 100 " +
            "            )) +" +
            // 加幅
            "      IF" +
            "         (" +
            "            thpisr.price_type != 2," +
            "         IF" +
            "            ( thpisr.price != 0, thpisr.price, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thpisr.price != 0, thpisr.price, 0 )/ 100 " +
            "            ))" +
            "   ) price," +
            // 获取酒店所有房间数
            "   ( SELECT COUNT(*) FROM t_hotel_room_type WHERE hotel_id = thp.hotel_id ) roomNum " +
            "FROM" +
            "   t_hotel_product thp" +
            "   LEFT JOIN t_hotel_facilities thf ON thf.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_room_type thrt ON thrt.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_channel_online_self_platform thpcosp ON thpcosp.increase_strategy_id = thps.id " +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcosp.id " +
            "WHERE" +
            "   thp.id = #{req.id}" +
            "   AND " +
            // 成本价需要在用户输入的时间段范围内(通过判断产品成本是否在用户选择的所有时间段内,搜索出来的成本数要等于用户选择天数)
            "   (SELECT count(thpcd2.id) FROM t_hotel_product_cost thpc2 LEFT JOIN t_hotel_product_cost_date thpcd2 ON thpcd2.cost_id = thpc2.id WHERE thpc2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpcd2.date_begin &lt;= #{date} AND thpcd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND " +
            // 加幅策略需要在用户输入的时间段范围内(通过判断产品加幅是否在用户选择的所有时间段内,搜索出来的加幅数要等于用户选择天数)
            "   (SELECT count(thpsd2.id) FROM t_hotel_product_strategy thps2 LEFT JOIN t_hotel_product_strategy_date thpsd2 ON thpsd2.increase_strategy_id = thps2.id WHERE thps2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpsd2.date_begin &lt;= #{date} AND thpsd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   <if test=\"req.peopleNum != null\">" +
            "   AND thrt.max_adult &gt;= #{req.peopleNum}" +
            "   </if>" +
            // 查询C端渠道产品
            "   AND (thpcosp.channel_id = #{channelApp} OR thpcosp.channel_id = #{channelPc})" +
            // 查询审核通过的酒店产品
            "   AND thp.approval_status = 3" +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "   AND thp.sale_status = 1 " +
            "GROUP BY" +
            "   id" +
            "</script>")
    HotelProductDTO queryProductsH5ByProductid(@Param("req") RoomDetailsQueryH5Req req, @Param("dates") List<LocalDate> dates, @Param("channelApp") Long channelApp, @Param("channelPc") Long channelPc);

    /**
     * 获取酒店产品详情-套票查询使用
     * @param req
     * @return
     */
    @Select("<script>" +
            "SELECT" +
            "   thp.id productId," +
            "   thp.hotel_id," +
            "   thp.room_id," +
            // 获取酒店房间产品价格,实际售卖价格=成本价(需转换成HKD)+服务费+加幅
            "IF" +
            "   ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) AS costPrice," +
            "   thpisr.price taxation," +
            "      (" +
            // 成本价
            "      IF" +
            "         ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
            // 税费
             "      IF" +
             "         ( thp.taxation != 0, thp.taxation, 0 ) +" +
            // 服务费
            "      IF" +
            "         (" +
            "            thp.reserve_charge_type != 2," +
            "         IF" +
            "            ( thp.reserve_charge != 0, thp.reserve_charge, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thp.reserve_charge != 0, thp.reserve_charge, 0 )/ 100 " +
            "            )) +" +
            // 加幅
            "      IF" +
            "         (" +
            "            thpisr.price_type != 2," +
            "         IF" +
            "            ( thpisr.price != 0, thpisr.price, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thpisr.price != 0, thpisr.price, 0 )/ 100 " +
            "            ))" +
            "   ) price," +
            // 获取酒店所有房间数
            "   ( SELECT COUNT(*) FROM t_hotel_room_type WHERE hotel_id = thp.hotel_id ) roomNum," +
            // dateType==1跨多天查询
            "   <if test=\"dateType == 1\">" +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{checkInDate} AND use_time &lt;= #{checkOutDate}) = 0,thpc.stock,thpc.stock + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{checkInDate} AND use_time &lt;= #{checkOutDate})) stock," +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{checkInDate} AND use_time &lt;= #{checkOutDate} AND operator_id = #{relId}) = 0,thpisr.saleable_number,thpisr.saleable_number + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{checkInDate} AND use_time &lt;= #{checkOutDate})) remainSaleableNumber " +
            "   </if>" +
            // dateType==2按每天天查询
            "   <if test=\"dateType == 2\">" +
            // 获取酒店产品剩余库存(用+号是因为获取所用的库存数都是以-号开头,如果还用-号计算则会变成相加库存) todo 这个通过循环去查询库存会存在一定问题,后续需要优化处理
            "       <foreach collection='dates' item='date' open='(' separator=',' close=')'>" +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date}) = 0,thpc.stock,thpc.stock + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date})) " +
            "       </foreach>" +
            "   stock, " +
            // 获取用户剩余可售数(用+号是因为获取所用的可售数都是以-号开头,如果还用-号计算则会变成相加可售数) todo 这个通过循环去查询库存会存在一定问题,后续需要优化处理
            "       <foreach collection='dates' item='date' open='(' separator=',' close=')'>" +
            "           IF((SELECT COUNT(*) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date} AND operator_id = #{relId}) = 0,thpisr.saleable_number,thpisr.saleable_number + (SELECT SUM(stock_detail) FROM t_hotel_product_stock_record WHERE hotel_product_id = thp.id AND use_time &gt;= #{date} AND use_time &lt;= #{date})) " +
            "       </foreach>" +
            "   remainSaleableNumber " +
            "   </if>" +
            "FROM" +
            "   t_hotel_product thp" +
            "   LEFT JOIN t_hotel_facilities thf ON thf.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_room_type thrt ON thrt.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "   <if test=\"strategyType == 1\">" +
            "   LEFT JOIN t_hotel_product_channel_offline_self_custom thpcosc ON thpcosc.increase_strategy_id = thps.id " +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcosc.id " +
            "   </if>" +
            "   <if test=\"strategyType == 2\">" +
            "   LEFT JOIN t_hotel_product_channel_offline_agent_custom thpcoac ON thpcoac.increase_strategy_id = thps.id " +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcoac.id " +
            "   </if>" +
            "   <if test=\"strategyType == 3\">" +
            "   LEFT JOIN t_hotel_product_channel_online_self_platform thpcosp ON thpcosp.increase_strategy_id = thps.id " +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcosp.id " +
            "   </if>" +
            "   <if test=\"strategyType == 4\">" +
            "   LEFT JOIN t_hotel_product_channel_online_third_platform thpcotp ON thpcotp.increase_strategy_id = thps.id " +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcotp.id " +
            "   </if>" +
            "WHERE" +
            "   thp.id = #{productId}" +
            "   AND " +
            // 成本价需要在用户输入的时间段范围内(通过判断产品成本是否在用户选择的所有时间段内,搜索出来的成本数要等于用户选择天数)
            "   (SELECT count(thpcd2.id) FROM t_hotel_product_cost thpc2 LEFT JOIN t_hotel_product_cost_date thpcd2 ON thpcd2.cost_id = thpc2.id WHERE thpc2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpcd2.date_begin &lt;= #{date} AND thpcd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND " +
            // 加幅策略需要在用户输入的时间段范围内(通过判断产品加幅是否在用户选择的所有时间段内,搜索出来的加幅数要等于用户选择天数)
            "   (SELECT count(thpsd2.id) FROM t_hotel_product_strategy thps2 LEFT JOIN t_hotel_product_strategy_date thpsd2 ON thpsd2.increase_strategy_id = thps2.id WHERE thps2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpsd2.date_begin &lt;= #{date} AND thpsd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            // 查询产品指定可销售的人员
            // 线下自营
            "   <if test=\"strategyType == 1\">" +
            "   AND IF(self_sale_permission_type = 2,1=1,thpcosc.relation_id IN " +
            "       <foreach collection='relationIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" + " )" +
            "   </if>" +
            // 线下代理商
            "   <if test=\"strategyType == 2\">" +
            "   AND IF(self_sale_permission_type = 2,1=1,thpcoac.relation_id IN " +
            "       <foreach collection='relationIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" + " )" +
            "   </if>" +
            // 线上自营渠道
            "   <if test=\"strategyType == 3\">" +
            "   AND (thpcosp.channel_id = #{relId})" +
            "   </if>" +
            // 线上第三方渠道
            "   <if test=\"strategyType == 4\">" +
            "   AND (thpcotp.platform_id = #{relId})" +
            "   </if>" +
            // 查询审核通过的酒店产品
            "   AND thp.approval_status = 3" +
            "   AND thp.is_deleted = 0 " +
            "   AND thp.on_shelf_status = 1 " +
            "GROUP BY" +
            "   productId" +
            "</script>")
    HotelStockDTO querySeasonProductsByProductId(@Param("strategyType") int strategyType, @Param("productId") long productId, @Param("dates") List<LocalDate> dates, @Param("checkInDate") LocalDate checkInDate, @Param("checkOutDate") LocalDate checkOutDate, @Param("roomNum") int roomNum, @Param("relId") long relId, @Param("relationIds") List<Long> relationIds, @Param("dateType") int dateType);

    @Select("<script>"+
            "SELECT" +
            "   thp.id," +
            "   thp.taxation," +
            "      (" +
            "      IF" +
            "         ( thpc.price != 0, thpc.price * thpc.exchange_rate, 0 ) +" +
            "      IF" +
            "         (" +
            "            thp.reserve_charge_type != 2," +
            "         IF" +
            "            ( thp.reserve_charge != 0, thp.reserve_charge, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thp.reserve_charge != 0, thp.reserve_charge, 0 )/ 100 " +
            "            )) +" +
            "      IF" +
            "         (" +
            "            thpisr.price_type != 2," +
            "         IF" +
            "            ( thpisr.price != 0, thpisr.price, 0 )," +
            "            thpc.price *(" +
            "            IF" +
            "               ( thpisr.price != 0, thpisr.price, 0 )/ 100 " +
            "            ))" +
            "   ) unitPrice " +
            "FROM" +
            "   t_hotel_product thp" +
            "   LEFT JOIN t_hotel_facilities thf ON thf.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_room_type thrt ON thrt.hotel_id = thp.hotel_id" +
            "   LEFT JOIN t_hotel_product_cost thpc ON thpc.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_cost_date thpcd ON thpcd.cost_id = thpc.id" +
            "   LEFT JOIN t_hotel_product_strategy thps ON thps.hotel_product_id = thp.id" +
            "   LEFT JOIN t_hotel_product_strategy_date thpsd ON thpsd.increase_strategy_id = thps.id" +
            "<if test=\"req.ordersType != null and req.ordersType == 1\">" +
            "   LEFT JOIN t_hotel_product_channel_offline_self_custom thpcos ON thpcos.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcos.id " +
            "</if>" +
            "<if test=\"req.ordersType != null and req.ordersType == 2\">" +
            "   LEFT JOIN t_hotel_product_channel_offline_agent_custom thpcoa ON thpcoa.increase_strategy_id = thps.id" +
            "   LEFT JOIN t_hotel_product_increase_strategy_rule thpisr ON thpisr.custom_id = thpcoa.id " +
            "</if>" +
            "WHERE" +
            "   thp.id = #{req.hotelProductId}" +
            "   AND " +
            // 成本价需要在用户输入的时间段范围内(通过判断产品成本是否在用户选择的所有时间段内,搜索出来的成本数要等于用户选择天数)
            "   (SELECT count(thpcd2.id) FROM t_hotel_product_cost thpc2 LEFT JOIN t_hotel_product_cost_date thpcd2 ON thpcd2.cost_id = thpc2.id WHERE thpc2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpcd2.date_begin &lt;= #{date} AND thpcd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND " +
            // 加幅策略需要在用户输入的时间段范围内(通过判断产品加幅是否在用户选择的所有时间段内,搜索出来的加幅数要等于用户选择天数)
            "   (SELECT count(thpsd2.id) FROM t_hotel_product_strategy thps2 LEFT JOIN t_hotel_product_strategy_date thpsd2 ON thpsd2.increase_strategy_id = thps2.id WHERE thps2.hotel_product_id = thp.id AND " +
            "       <foreach collection='dates' item='date' open='(' separator='OR' close=')'>" +
            "           (thpsd2.date_begin &lt;= #{date} AND thpsd2.date_end &gt;= #{date}) " +
            "       </foreach>" +
            "   ) = ${dates.size} " +
            "   AND thp.approval_status = 3" +
            // ordersType==1则查询相关散客价格
            "<if test=\"req.ordersType != null and req.ordersType == 1\">" +
                // 不查询禁售产品
            "   AND thps.self_sale_permission_type &lt;&gt; 1" +
                // 查询产品指定可销售的人员
            "   AND IF(thps.self_sale_permission_type = 2,1=1,thpcos.relation_id IN " +
            "       <foreach collection='relationIds' item='id' open='(' separator=',' close=')'>" +
            "           #{id}" +
            "       </foreach>" + " )" +
            "</if>" +
            // ordersType==2则查询相关代理商价格
            "<if test=\"req.ordersType != null and req.ordersType == 2\">" +
                // 不查询禁售产品
            "   AND thps.agent_sale_permission_type &lt;&gt; 1" +
                // 查询产品指定可销售的人员
            "   AND IF(thps.agent_sale_permission_type = 2,1=1,thpcoa.agent_id = #{req.buyerId})" +
            "</if>" +
            "   AND thp.is_deleted = 0 " +
            "GROUP BY" +
            "   id" +
            "</script>")
    HotelProductUnitPriceDTO queryProductUnitPrice(@Param("relationIds") List<Long> relationIds, @Param("req") HotelProductUnitPriceReq req, @Param("dates") List<LocalDate> dates);

    @Select("<script>"+
            "select h.id as hotelId, h.name as hotelName,h.en_name as hotelNameEn,h.location, h.star_level,h.star_level_id, b.id productId,b.supplier_id,b.source_type,b.min_price productPrice\n" +
            "from t_hotel_product_cost a\n" +
            "left join t_hotel_product b on a.hotel_product_id = b.id\n" +
            "left join t_hotel_product_cost_date c on a.id  = c.cost_id\n" +
            "left join t_hotel h on b.hotel_id = h.id\n" +
            "WHERE b.is_deleted = 0\n" +
            "AND b.is_recommend = 1\n" +
            "<if test=\"req.cityName != null and req.cityName != ''\"> and (t_hotel.city_name LIKE CONCAT('%',#{req.cityName},'%'))</if> \n"+
            "<if test=\"req.hotelName != null and req.hotelName != ''\"> and (h.name LIKE CONCAT('%',#{req.hotelName},'%'))</if> \n"+
            "<if test=\"req.roomNum != null and req.roomNum != ''\"> and t_hotel_product_cost_date.stock >= #{req.roomNum}</if> \n"+
            "<if test=\"req.checkInDate != null\"> and #{req.checkInDate} between c.date_begin and c.date_end </if> \n"+
            "<if test=\"req.starLevel != null and req.starLevel != ''\"> and t_hotel.star_level = #{req.starLevel}</if> \n"+
            "group by b.id order by b.gmt_create desc"+
            "</script>")
    Page<RecommendedHotelDTO> queryAllRecommendedHotel(HotelReservertionReq req, Page<RecommendedHotelDTO> page);

    /**
     * 查询酒店产品详情列表
     * @return
     */
    @Select("<script>"+
            "select h.id as hotelId,b.id as productId,p.cover_img as coverImg,p.photo_json as photoJson,p.name as roomName,p.en_name as roomEnName,p.max_adult as maxAdult,b.target_population as targetPopulation,b.id productId,b.supplier_name as supplierName,a.stock as stock,a.price as price\n" +
            "from t_hotel_product_cost a\n" +
            "left join t_hotel_product b on a.hotel_product_id = b.id\n" +
            "left join t_hotel_product_cost_date c on a.id  = c.cost_id\n" +
            "left join t_hotel h on b.hotel_id = h.id\n" +
            "left join t_hotel_room_type p on b.hotel_id = p.hotel_id\n" +
            "WHERE b.is_deleted = 0\n" +
            "AND c.date_begin =CURDATE() and c.date_end =CURDATE()\n" +
            "group by b.id order by b.gmt_create desc"+
            "</script>")
    List<HotelReservestionProductListDetailDTO> queryHotelReservestionProductListDetailDTO();
}
